import mysql.connector as mc
from mysql.connector import errorcode
import numpy as np
import datetime
import time

config = {
  'user': 'root',
  'password': 'fxqs123',
  'host': '127.0.0.1',
  'database': 'acrecord',
  'raise_on_warnings': True,
}

class Mysql():
    def __init__(self,name):
        try:
            self.cnx = mc.connect(**config)
        except mc.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("用户名密码错误")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("数据库不存在")
            else:
                print(err)
        else:
            print('线程{}数据库{}已连接'.format(name,config['database']))
            self.cursor=self.cnx.cursor()

    def Creat_table(self):
        name='Experience-'+time.strftime("%m-%d-%H-%M", time.localtime())
        table_form='CREATE TABLE `acrecord`.`'+name+'` ('\
                      '`ID` INT NOT NULL AUTO_INCREMENT,'\
                      '`EP` INT NOT NULL,'\
                      '`Step` INT NOT NULL,'\
                      '`Type` VARCHAR(45) NOT NULL,'\
                      '`Entity_Number` INT NOT NULL,'\
                      '`X` FLOAT NOT NULL,'\
                      '`Y` FLOAT NOT NULL,'\
                      '`Z` FLOAT NOT NULL,'\
                      '`Pointing_theta` FLOAT NULL,'\
                      '`Pointing_phi` FLOAT NULL,'\
                      '`Poss_Destroy` FLOAT NULL,'\
                      '`BelongTo` INT NULL,'\
                      '`Activity` INT NULL,'\
                      '`DestroyTarget` INT NULL,'\
                      '`Target` INT NULL,'\
                      '`N_ammo` INT NULL,'\
                      '`CD` INT NULL,'\
                      '`decision_1` FLOAT NULL,'\
                      '`decision_2` FLOAT NULL,'\
                      '`decision_3` FLOAT NULL,'\
                      '`decision_4` FLOAT NULL,'\
                      '`decision_5` FLOAT NULL,'\
                      'PRIMARY KEY (`ID`),'\
                      'UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE);'



        try:
            print("创建表格 {}:".format(name),end='')
            self.cursor.execute(table_form)
        except mc.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("该表已存在")
            else:
                print(err.msg)
        else:
            print("OK")
        return name

    def Insert_record(self,name,data):
        add_recordtable = ("INSERT INTO `"+name+"` "
                        "(EP, Step, Type, Entity_Number, X,Y,Z,Pointing_theta,Pointing_phi,Poss_Destroy,"
                                               "BelongTo,Activity,DestroyTarget,Target,N_ammo,CD,"
                                               "decision_1,decision_2,decision_3,decision_4,decision_5) "
                        "VALUES (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s,%s,%s, %s, %s, %s,%s, %s, %s, %s)")
        if type(data)==tuple:
            try:
                print('添加记录：',end='')
                self.cursor.execute(add_recordtable,data)
            except mc.Error as err:
                print(err.msg)
            else:
                self.cnx.commit()
                print('成功')

        if type(data)==list:
            try:
                print('添加记录：',end='')
                self.cursor.executemany(add_recordtable,data)
            except mc.Error as err:
                print(err.msg)
            else:
                self.cnx.commit()
                print('成功')

    def Insert_index(self,data):
        add_indextable = ("INSERT INTO main_index "
                        "(start_time,n_friend,n_enemy,max_ep,max_step,table_index,end_time) "
                        "VALUES (%s, %s, %s, %s, %s,%s,%s)")
        try:
            print('添加索引表记录：',end='')
            self.cursor.execute(add_indextable,data)
        except mc.Error as err:
            print(err.msg)
        else:
            self.cnx.commit()
            print('成功')

    def EndTime(self,end_time,sqlname):
        try:
            print('修改结束时间:',end='')
            update_indextable=("update main_index set end_time='{}' where table_index='{}'".format(
                end_time,sqlname))
            self.cursor.execute(update_indextable)
        except mc.Error as err:
            print(err.msg)
        else:
            self.cnx.commit()
            print('成功')

    def Close(self):
        self.cursor.close()
        self.cnx.close()


######################################测试用例#################################################
if __name__=='__main__':
    conn=Mysql('test')
    name=conn.Creat_table()
    index_data=(datetime.datetime.now(),3,3,3,3,name,datetime.datetime.now())
    conn.Insert_index(index_data)
    table_data=[0, 1, 'sam', 123, 1.1,2.2,3.3,0.23,0.1,0.2,
                 234,1,1,123,2,5,0.33,0.22,0.11,0.53,0.63]
    datas=[table_data,table_data,table_data]
    conn.Insert_record(name,datas)
    conn.EndTime(datetime.datetime(2018, 6, 14),name)


